USE Liberty
GO

-- ------------------------------------------------------------------------------------------------
-- Grants Table Create Script
-- ------------------------------------------------------------------------------------------------
SET NOCOUNT OFF
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
SET ARITHABORT ON
SET CONCAT_NULL_YIELDS_NULL ON
SET QUOTED_IDENTIFIER ON
SET NUMERIC_ROUNDABORT OFF
GO

PRINT 'Creating Table dbo.Grants...'
GO

IF OBJECT_ID('dbo.Grants', 'U') IS NULL
BEGIN
	CREATE TABLE dbo.Grants
	(OID				bigint		IDENTITY(1, 1)	NOT NULL
	,Version			rowversion
	,CaseID				bigint						NOT NULL
	,ApplicantID		bigint						NOT NULL
	,ValueOfGrant		decimal(11, 2)				NOT NULL
	,CheckNumber		varchar(20)						NULL
	,NameOnCreditCard	varchar(128)					NULL
	,GrantTypeID		bigint						NOT NULL
	,StateID			bigint						NOT NULL
	,PaymentTypeID		bigint						NOT NULL
	,DeliveryMethodID	bigint						NOT NULL
	,IssueToPersonID	bigint						NOT NULL
	,IssueToAddressID	bigint						NOT NULL
	,DeliverToPersonID	bigint						NOT NULL
	,DeliverToAddressID	bigint						NOT NULL
	,Approver1			bigint							NULL
	,ApprovalDate1		datetime						NULL
	,Approver2			bigint							NULL
	,ApprovalDate2		datetime						NULL
	,DisbursedBy		bigint							NULL
	,DispersalDate		datetime						NULL
	,VerifiedBy			bigint							NULL
	,VerificationDate	datetime						NULL

	,UpdatedDate		datetime					NOT NULL
		CONSTRAINT DFLT_Grants_UpdatedDate		DEFAULT GETDATE()
	,CreatedDate		datetime					NOT NULL
		CONSTRAINT DFLT_Grants_CreatedDate		 DEFAULT GETDATE()

	,CONSTRAINT PK_Grants PRIMARY KEY CLUSTERED( OID ) --ON PRIMARY

	,CONSTRAINT FK_Grants_Cases FOREIGN KEY (CaseID)
		REFERENCES dbo.Cases(OID)

	,CONSTRAINT FK_Grants_Applicants FOREIGN KEY (ApplicantID)
		REFERENCES dbo.Applicants(OID)

	,CONSTRAINT FK_Grants_TypesOfGrant FOREIGN KEY (GrantTypeID)
		REFERENCES dbo.TypesOfGrant(OID)

	,CONSTRAINT FK_Grants_StatesOfGrant FOREIGN KEY (StateID)
		REFERENCES dbo.StatesOfGrant(OID)

	,CONSTRAINT FK_Grants_TypesOfPayment FOREIGN KEY (PaymentTypeID)
		REFERENCES dbo.TypesOfPayment(OID)

	,CONSTRAINT FK_Grants_TypesOfDeliveryMethod FOREIGN KEY (DeliveryMethodID)
		REFERENCES dbo.TypesOfDeliveryMethod(OID)

	,CONSTRAINT FK_Grants_People1 FOREIGN KEY (IssueToPersonID)
		REFERENCES dbo.People(OID)

	,CONSTRAINT FK_Grants_People2 FOREIGN KEY (DeliverToPersonID)
		REFERENCES dbo.People(OID)

	,CONSTRAINT FK_Grants_Addresses1 FOREIGN KEY (IssueToAddressID)
		REFERENCES dbo.Addresses(OID)

	,CONSTRAINT FK_Grants_Addresses2 FOREIGN KEY (DeliverToAddressID)
		REFERENCES dbo.Addresses(OID)

	,CONSTRAINT FK_Grants_Users1 FOREIGN KEY (Approver1)
		REFERENCES dbo.Users(OID)

	,CONSTRAINT FK_Grants_Users2 FOREIGN KEY (Approver2)
		REFERENCES dbo.Users(OID)

	,CONSTRAINT FK_Grants_Users3 FOREIGN KEY (DisbursedBy)
		REFERENCES dbo.Users(OID)

	,CONSTRAINT FK_Grants_Users4 FOREIGN KEY (VerifiedBy)
		REFERENCES dbo.Users(OID)

	)

	PRINT 'Table dbo.Grants has been created successfully.'

END
ELSE 
BEGIN
    PRINT 'Table dbo.Grants already exists.'
END

GO

-- DROP TABLE dbo.GrantAssignments
-- DROP TABLE dbo.Documents
-- DROP TABLE dbo.Grants
